package UserDatabase;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JOptionPane;
import javax.swing.JPanel;

import com.mysql.cj.jdbc.result.ResultSetMetaData;

import function.BillFrame;
import function.CardFrame;
import function.UserFrame;

public class BillDatabase {

	Connection connect = null;
	Statement stem = null;
	ResultSet rs =null;
	
	String SQL = "";
	String columnName[];
	String record[][];
	int columnCount,recordAmount;
	
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/property_system","root","yang2532661314");
			//System.out.println(connect);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connect;
	}
	
	//按用户更新账单
	public void update_Id(Bill b) {
		connect = getConnection();
		try {
			stem = connect.createStatement();
			int id = b.getId();
			int water = b.getWater();
			int electricity = b.getElectricity();
			int gas = b.getGas();
			int wbill = b.getWbill();
			int pbill = b.getPbill();
			int hbill = b.getHbill();
			stem.executeUpdate("UPDATE Bill set 水费 = 水费 + "+water+" , 电费 = 电费+"+electricity+" , 燃气费 = 燃气费+"+gas+" , 取暖费 = 取暖费+"+wbill+" , 物业费 = 物业费+"+pbill+" , 房租 = 房租+"+hbill+" where Id = "+id);                               
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();	
		}
		closegdbc();
	}
	
	//更新所有人账单
	public void update_All(Bill b) {
		connect = getConnection();
		try {
			stem = connect.createStatement();
			int water = b.getWater();
			int electricity = b.getElectricity();
			int gas = b.getGas();
			int wbill = b.getWbill();
			int pbill = b.getPbill();
			int hbill = b.getHbill();
			stem.executeUpdate("UPDATE Bill set 水费 = 水费+ "+water+" , 电费 = 电费+"+electricity+" , 燃气费 = 燃气费+"+gas+", 取暖费 = 取暖费+"+wbill+" , 物业费 = 物业费+"+pbill+" , 房租 = 房租+"+hbill+" where Id IN (select Id from user where type = '业主')");                           
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();	
		}
		closegdbc();
	}
	
	//缴费
		public void update_buy(String Username) {
			connect = getConnection();
			String name = Username;
			int sum =0;
			int money = 0;
			try {
				stem = connect.createStatement();
				rs = stem.executeQuery("select * from bill where Id = (select id from user where name = '"+name+"')");	
				while(rs.next()) {
					int water = rs.getInt("水费");
					int electricity = rs.getInt("电费");
					int gas = rs.getInt("燃气费");
					int wbill = rs.getInt("取暖费");
					int pbill = rs.getInt("物业费");
					int hbill = rs.getInt("房租");
					sum = water+electricity+gas+wbill+pbill+hbill;
				}
				rs = stem.executeQuery("select * from Card where Id = (select id from user where name = '"+name+"')");	
				while(rs.next()) {
					money = rs.getInt("剩余金额");
				}
				
				if(money>=sum) {
					money = money - sum;
					stem.executeUpdate("UPDATE Card set 剩余金额 = "+money+" where id = (select id from User where name = '"+name+"')");
					stem.executeUpdate("UPDATE Bill set 水费 = 0 , 电费 = 0, 燃气费 = 0, 取暖费 = 0 , 物业费 = 0 , 房租 = 0 where Id IN (select id from user where name = '"+name+"')");    
					JPanel f = new JPanel();
					JOptionPane.showMessageDialog(f,"缴费成功！","提示框",JOptionPane.WARNING_MESSAGE);	
					new BillFrame("select user.id,name,age,user.uno,user.hno,水费,电费,燃气费,取暖费,物业费,房租 from user,bill,house where user.hno = house.hno and bill.id = user.id and type = '业主' and name = '"+Username+"' and (水费!=0 OR 电费!=0 OR 燃气费!=0 OR 取暖费!=0 OR 物业费!=0 OR 房租!=0)",Username);
				}else {
					JPanel f = new JPanel();
					JOptionPane.showMessageDialog(f,"卡里余额不足请先充值！","提示框",JOptionPane.WARNING_MESSAGE);	
					new CardFrame("select * from card where id = (select id from user where name = '"+Username+"')",Username);
					
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();	
			}
			closegdbc();
		}
	
	//通用查询
		public void setSQL(String SQL) {
			this.SQL = SQL.trim();
		}
		
		public String[] getColumnName() {
			
			return columnName;
			
		}
		
		public String[][] getRecord(){
			
			startQuery();
			return record;
		}
		
		public void startQuery() {
			connect  = getConnection();
			try {
				stem = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
				rs = stem.executeQuery(SQL);
				ResultSetMetaData metadata = (ResultSetMetaData) rs.getMetaData();
				columnCount = metadata.getColumnCount();
				columnName = new String[columnCount];
				for(int i=1;i<=columnCount;i++) {
					columnName[i-1] = metadata.getColumnName(i);
				}
				rs.last();
				recordAmount = rs.getRow();
				record = new String[recordAmount][columnCount];
				int i = 0;
				rs.beforeFirst();
				while(rs.next()) {
					for(int j=1;j<=columnCount;j++) {
						record[i][j-1] = rs.getString(j);
					}
					i++;
				}
				closegdbc();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
		
		//关闭数据库
		
		public void closegdbc() {	
			try {
				if(connect!=null) {
					connect.close();	
				}
				if(rs!=null) {
					rs.close();	
				}
				if(stem!=null) {
					stem.close();	
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();	
			}	
		}
	
	
}
